package id.co.shuba.spamobile.data;

import id.co.shuba.spamobile.model.DSARInfo;
import id.co.shuba.spamobile.model.ProspectProgressInfo;

import java.text.ParseException;
import java.util.Date;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class ProspectProgressDB extends BaseDB {	
	
	public ProspectProgressDB(DatabaseHandler db) {
		super(db);
	}
	
	public Cursor getList(String prospectID) {
		String sql = "SELECT 1 AS  _id, O.OPTION_TEXT PROGRESS_STATUS, "
				+ "D.UPDATE_DATE FROM TBL_PROSPECT_PROGRESS D "
				+ "LEFT JOIN TBL_OPTION O ON O.OPTION_TYPE = ? "
				+ "AND O.OPTION_VALUE = D.PROGRESS_STATUS WHERE D.PROSPECT_ID = ?";

		String[] params = new String[] {
				String.valueOf(StandardDB.GROUP_PROGRESS_STATUS),
				String.valueOf(prospectID) };

		SQLiteDatabase db = _db.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, params);

		return cursor;
	}
	
	public Cursor getListByAoCode(String aoCode) {
		String sql = "SELECT PP.* FROM TBL_PROSPECT_PROGRESS PP INNER JOIN TBL_PROSPECT P ON PP.PROSPECT_ID = P.PROSPECT_ID WHERE P.AO_CODE = '"+aoCode+"'";	

		SQLiteDatabase db = _db.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, null);

		return cursor;
	}	
	
	public Cursor getListUpload(String aoCode) {
		String sql = "SELECT PP.* FROM TBL_PROSPECT_PROGRESS PP INNER JOIN TBL_PROSPECT P ON PP.PROSPECT_ID = P.PROSPECT_ID WHERE P.AO_CODE = '"+aoCode+"' AND PP.IS_UPLOAD = '0'";	

		SQLiteDatabase db = _db.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, null);

		return cursor;
	}			
	
	private static ContentValues createContentValues(ProspectProgressInfo info) {
		ContentValues values = new ContentValues();		
		values.put("PROSPECT_ID", info.getProspectID());
		values.put("PROGRESS_STATUS", info.getProgressStatus());			
		values.put("UPDATE_BY", info.getUpdateBy());
		values.put("UPDATE_DATE", info.getUpdateDate());
		values.put("IS_UPLOAD", "0");
		return values;
	}
	
	public static void add(SQLiteDatabase db, ProspectProgressInfo info) {
		ContentValues values = createContentValues(info);
		values.put("ENTRY_BY", info.getEntryBy());
		values.put("ENTRY_DATE", info.getEntryDate());
		db.insert("TBL_PROSPECT_PROGRESS", null, values);		
	}
	
	public static void addOrUpdate(SQLiteDatabase db, ProspectProgressInfo info) throws ParseException {
		ContentValues values = createContentValues(info);				
		values.put("ENTRY_BY", info.getEntryBy());
		values.put("ENTRY_DATE", info.getEntryDate());	
		values.put("UPLOAD_BY", info.getUploadBy());
		values.put("UPLOAD_DATE", info.getUploadDate());
		
		Cursor cursor = db.rawQuery("SELECT UPDATE_DATE FROM TBL_PROSPECT_PROGRESS WHERE PROSPECT_ID = ? AND PROGRESS_STATUS = ?",
				new String[] {info.getProspectID(), info.getProgressStatus() });
						
		Date upd = null;
		if (cursor != null && cursor.getCount() > 0) {
			cursor.moveToFirst();
			upd = cursorGetDate(cursor, "UPDATE_DATE");			
		}
		cursor.close();						
		
		if (upd == null) {			
			db.insert("TBL_PROSPECT_PROGRESS", null, values);
		} else if ( dateFormat.parse(info.getUpdateDate()).getTime() > upd.getTime()) {
			db.update("TBL_PROSPECT_PROGRESS", values, "PROSPECT_ID = ? AND PROGRESS_STATUS = ?",
					new String[] { info.getProspectID(),  String.valueOf(info.getProgressStatus())});
		}
	}	
	
	public static int updateUpload(SQLiteDatabase db, String prospectId, String progressStatus, String uploadBy) throws ParseException {
		ContentValues values = new ContentValues();
		values.put("IS_UPLOAD", "1");
		values.put("UPLOAD_DATE", getCurrentDateTime());
		values.put("UPLOAD_BY", uploadBy);
		
		return db.update("TBL_PROSPECT_PROGRESS", values, "PROSPECT_ID = ? AND PROGRESS_STATUS = ?",
				new String[] { prospectId, progressStatus});
	}	
	
	public void add(ProspectProgressInfo info) {
		SQLiteDatabase db = _db.getWritableDatabase();
		ContentValues values = createContentValues(info);
		values.put("ENTRY_BY", info.getEntryBy());
		values.put("ENTRY_DATE", info.getEntryDate());
		db.insert("TBL_PROSPECT_PROGRESS", null, values);
	}

	public void update(ProspectProgressInfo info) {
		ContentValues values = createContentValues(info);							

		SQLiteDatabase db = _db.getWritableDatabase();

		db.update("TBL_PROSPECT_PROGRESS", values, "PROSPECT_ID = ? AND PROGRESS_STATUS = ?",
				new String[] { info.getProspectID(), info.getProgressStatus() });
	}	
	
	public void delete(String prospectId, String progressStatus) {		
		SQLiteDatabase db = _db.getWritableDatabase();

		db.delete("TBL_PROSPECT_PROGRESS", "PROSPECT_ID = ? AND PROGRESS_STATUS = ?", 
				new String[] { prospectId, progressStatus });
	}
	
	public ProspectProgressInfo get(String prospectId, String progressStatus) {
		SQLiteDatabase db = _db.getReadableDatabase();

		Cursor cursor = db.rawQuery("SELECT * FROM TBL_PROSPECT_PROGRESS WHERE PROSPECT_ID = ? AND PROGRESS_STATUS = ?",
				new String[] { prospectId, progressStatus });

		if (cursor != null && cursor.getCount() > 0)
			cursor.moveToFirst();
		else {
			cursor.close();
			return null;
		}

		ProspectProgressInfo info = new ProspectProgressInfo();
		info.setProspectID(prospectId);
		info.setProgressStatus(progressStatus);		
		info.setEntryBy(cursorGetString(cursor, "ENTRY_BY"));
		info.setEntryDate(cursorGetString(cursor, "ENTRY_DATE"));
		info.setUpdateBy(cursorGetString(cursor, "UPDATE_BY"));
		info.setUpdateDate(cursorGetString(cursor, "UPDATE_DATE"));
		cursor.close();

		return info;
	}		
}
